CREATE OR REPLACE PACKAGE LOTSPLIT IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================
TYPE TYPE_LotSplit_Lot IS RECORD
(
    ContainerName Container.ContainerName%TYPE
    , ContainerId Container.ContainerId%TYPE
    , SpecName specbase.specname%TYPE
    , InprocessStatus VARCHAR2(40)
    , MTFirstStep specbase.specname%TYPE
    , LotCategory Container.LotCategory%TYPE
)
;
TYPE COLL_LotSplit_Lot IS TABLE OF TYPE_LotSplit_Lot INDEX BY BINARY_INTEGER
;
TYPE TYPE_LotSplit_InputData IS RECORD
(
    MainLotId Container.ContainerName%TYPE
    , ChildLotIds TRGUtil.COLLECTION
)
;
TYPE TYPE_LotSplit_ServiceData IS RECORD
(
    MainLot TYPE_LotSplit_Lot
)
;
--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION VerifyData (MainLotId IN VARCHAR2
                     , ChildLotIds IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE VerifyData2 (MainLotId IN VARCHAR2
                       , ChildLotIds IN VARCHAR2)
;
FUNCTION ExecuteData (ContainerNames IN VARCHAR2)
RETURN VARCHAR2
;
PROCEDURE ExecuteData2 (ContainerNames IN VARCHAR2)
;
PROCEDURE VerifyAttributes (InputData IN TYPE_LotSplit_InputData
                            , ServiceData IN TYPE_LotSplit_ServiceData)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY LOTSPLIT AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- VerifyData
--=====================
FUNCTION VerifyData (MainLotId IN VARCHAR2
                     , ChildLotIds IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    VerifyData2 (MainLotId
                 , ChildLotIds);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        RETURN ('LotSplit.VerifyData->Exited prematurely');
    WHEN eAppError THEN
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        RETURN ('LotSplit.VerifyData->' || SQLERRM);
END VerifyData;

--=====================
-- VerifyData2
--=====================
PROCEDURE VerifyData2 (MainLotId IN VARCHAR2
                       , ChildLotIds IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLOT (IN_ContainerName Container.ContainerName%TYPE) IS
    SELECT
        A.ContainerName
        , A.ContainerId
        , D.specname
        , '' InprocessStatus
        , '' MTFirstStep
        , A.lotcategory
    FROM
        Container A,
        Currentstatus B,
        Spec C,
        Specbase D
    WHERE
        A.currentstatusid=B.currentstatusid
        and B.specid=C.specid
        and C.specbaseid=D.specbaseid
        and A.ContainerName = IN_ContainerName;
    
    CURSOR curTrackstatus (IN_ContainerName Container.ContainerName%TYPE) IS
    SELECT 
        A.trackstatus 
    FROM 
        A_WIPEQUIPMENT A,
        CONTAINER B 
    WHERE 
        A.containerid=B.containerid 
        AND a.trackstatus = 'INPROCESS' 
        AND b.containername= IN_ContainerName;
    
    CURSOR curSpecName (IN_ContainerName Container.ContainerName%TYPE) IS
    select specname from (
    select g.specname from container a,currentstatus b,workflowstep c,workflow d,workflowstep e,specbase g,spec f
    where a.currentstatusid=b.currentstatusid and b.workflowstepid=c.workflowstepid and c.workflowid=d.workflowid 
    and d.workflowid=e.workflowid and e.specbaseid=g.specbaseid and a.containername=IN_ContainerName and g.specbaseid=f.specbaseid
    and g.specname>3080 and f.ObjectCategory = 'WIP' 
    order by e.sequence)
    where rownum=1;
    --=====================
    -- Local variables
    --=====================
    tInputData TYPE_LotSplit_InputData;
    tServiceData TYPE_LotSplit_ServiceData;
    nCount NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Save the input variables
    --=====================
    tInputData.MainLotId := MainLotId;
    tInputData.ChildLotIds := TRGUtil.SetCollection(ChildLotIds, ',');
    --=====================
    -- Fetch Main Lot information
    --=====================
    OPEN curLot(tInputData.MainLotId);
    FETCH curLot INTO tServiceData.MainLot;
    CLOSE curLot;
    
    OPEN curTrackstatus(tInputData.MainLotId);
    LOOP
          EXIT WHEN curTrackstatus%NOTFOUND;
          FETCH curTrackstatus
          INTO tServiceData.MainLot.InprocessStatus; 
    END LOOP;
    CLOSE curTrackstatus;
    
    OPEN curSpecName(tInputData.MainLotId);
    LOOP
          EXIT WHEN curSpecName%NOTFOUND;
          FETCH curSpecName
          INTO tServiceData.MainLot.MTFirstStep; 
    END LOOP;
    CLOSE curSpecName;
    
    --=====================
    -- Verify data
    --=====================
    VerifyAttributes (tInputData, tServiceData);
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotSplit.VerifyData2->' || SQLERRM);
END VerifyData2;

--=====================
-- ExecuteData
--=====================
FUNCTION ExecuteData (ContainerNames IN VARCHAR2)
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Call the procedure
    --=====================
    ExecuteData2 (ContainerNames);
    --=====================
    -- Return success
    --=====================
    RETURN ('0');
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('LotSplit.ExecuteData->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('LotSplit.ExecuteData->' || SQLERRM);
END ExecuteData;

--=====================
-- ExecuteData2
--=====================
PROCEDURE ExecuteData2 (ContainerNames IN VARCHAR2)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    --
    --=====================

    --=====================
    -- Commit the data
    --=====================
    COMMIT;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'LotSplit.ExecuteData2->' || SQLERRM);
END ExecuteData2;

--=====================
-- VerifyAttributes
--=====================
PROCEDURE VerifyAttributes (InputData IN TYPE_LotSplit_InputData
                            , ServiceData IN TYPE_LotSplit_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Local variables
    --=====================
    nCount NUMBER;
    tChildLotData TYPE_LotSplit_Lot;
    nDoNothingForNow NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- if main lot is inprocess,cannot allow to combine
    -- Bryan
    -- 2010-07-07
    --=====================
    --IF ServiceData.MainLot.InprocessStatus = 'INPROCESS' THEN
    --RAISE_APPLICATION_ERROR (-20005, 'LotCombine_E1012 ' || tChildLotData.ContainerName );
    --END IF;
        
    --=====================
    -- after MT first step.cannot allow to combine
    -- Bryan
    -- 2010-07-07
    --=====================
    IF TO_NUMBER(ServiceData.MainLot.SpecName) > TO_NUMBER(ServiceData.MainLot.MTFirstStep) 
      AND TO_NUMBER(ServiceData.MainLot.SpecName) < 6000 AND ServiceData.MainLot.SpecName NOT IN ('5033','5045')
      AND ServiceData.MainLot.LotCategory != 'Q' AND (ServiceData.MainLot.ContainerName NOT LIKE 'Q%')
      AND ServiceData.MainLot.ContainerName NOT IN ('M0B26M85F9','M0B27M8F87') THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotSplit_E0007 ' || tChildLotData.ContainerName );
    END IF;

    --=====================
    -- Verify each Child Lot against the Main Lot
    --=====================
    FOR nCount IN 0..InputData.ChildLotIds.COUNT-1 LOOP
        nDoNothingForNow := nCount;
    END LOOP;
EXCEPTION
    WHEN eAppError THEN
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR (-20005, 'LotSplit.VerifyAttributes->' || SQLERRM);
END VerifyAttributes;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
